package shg.book.database
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.display.BitmapData;
	import flash.errors.SQLError;
	import flash.events.Event;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	import flash.geom.Matrix;
	import flash.net.dns.AAAARecord;
	import flash.utils.ByteArray;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Image;
	import mx.graphics.ImageSnapshot;
	import mx.graphics.codec.JPEGEncoder;
	import mx.utils.UIDUtil;
	
	import shg.book.fitment.FitmentSHG;
	import shg.book.size.BookData;
	import shg.book.socket.SocketDate;
	import shg.book.util.ImageLoader;
	import shg.book.view.ReadTextBook;
	
	/**
	 * 当阅读器启动的时候该类将会被调用！如果当前登录这个用户是第一次在本机启动的话就会创建
	 * 一个以当前登录的用户名创建一个数据库，如果该用户以前登录过的话就需要查询当前登录这个用
	 * 户的以前留在本地上的数据，把他搜索显示在桌面上。同时也会到服务器撒谎能够获取数据信息
	 * shuhg这个数据库里面的所有数据都是共享的
	 * */
	public class ConnectionDatabase
	{
		//异步打开文件
		static private var conn:SQLConnection;
		
		private var loaderFile:ImageLoader;
		private var bookArray:ArrayCollection;
		private var fitement:FitmentSHG;
		private var readTextBook:ReadTextBook;
		
		public function ConnectionDatabase()
		{
		}
		public function loaderSwf(event:Event):void{
			var imageWidth:uint = event.currentTarget.width;
			var imageHeigh:uint = event.currentTarget.height;
			//创建一个具有指定高度和宽度的BitmapData对象75/100
			var srcBmp:BitmapData = new BitmapData(BookData._WIDTH,BookData._HEIGTH);
			//或者指定路径的图片
			var m:Matrix=new Matrix();
			m.scale(BookData._WIDTH/imageWidth,BookData._HEIGTH/imageHeigh);
			srcBmp.draw(Image(event.currentTarget),m);
			var jpg:JPEGEncoder = new JPEGEncoder(100);
			var bytes:ByteArray = jpg.encode(srcBmp);
			//插入之前先把原来的数据删除掉
			delect(BookData.bookID);
			//插入数据
			insert(0,0,BookData.bookID,BookData.filePath,bytes,"shg");
		}
		/**
		 * 连接数据库
		 * */
		public function initApp(flexBook:shuhg=null,userName:String=null):void{
			conn = new SQLConnection(); 
			conn.addEventListener(SQLEvent.OPEN, openSuccess); 
			conn.addEventListener(SQLErrorEvent.ERROR, openFailure); 
			var dbFile:File =File.applicationStorageDirectory.resolvePath(SocketDate.userName+".db");// File.applicationDirectory.resolvePath("Users//SHUHG.DB"); 
			trace(dbFile.nativePath);
			conn.open(dbFile);
		}
		/**
		 * 如果连接上数据最后就
		 * */
		private function openSuccess(event:SQLEvent):void{
			var createStmt:SQLStatement = new SQLStatement(); 
			createStmt.sqlConnection = SQLConnection(event.currentTarget);
			//这是一个刚刚的的数据库表，里面的数据哪个用户都可以访问
			var sql:String =  
				"CREATE TABLE IF NOT EXISTS huahuang (" +  
				"    ID TEXT, " +  
				"    X INTEGER, " +  
				"    Y INTEGER, " +  
				"	 INDEXPAGE INTEGER, " +
				"    FILEPATH TEXT," +  
				"    IMAGE  BLOB," +  
				"    USERNAME  TEXT" +  
				")"; 
			createStmt.text = sql; 
			createStmt.addEventListener(SQLEvent.RESULT, createResult); 
			createStmt.addEventListener(SQLErrorEvent.ERROR, createError); 
			createStmt.execute(); 
			//从数据库里面查询数据信息
			select();
		}
		/**
		 * 插入数据
		 * */
		private function insert(x:int,y:int,bookID:String,filePath:String,byte:ByteArray,userName:String):void{
			var insertStmt:SQLStatement = new SQLStatement(); 
			insertStmt.sqlConnection = conn; 
			var sql:String =  
				"INSERT INTO huahuang (ID,X,Y,FILEPATH,IMAGE,USERNAME) " +  
				"VALUES (:ID,:X,:Y, :FILEPATH, :IMAGE,:USERNAME)"; 
			insertStmt.parameters[":ID"]=bookID;
			insertStmt.parameters[":X"]=x;
			insertStmt.parameters[":Y"]=y;
			insertStmt.parameters[":FILEPATH"]=filePath;
			insertStmt.parameters[":IMAGE"]=byte;
			insertStmt.parameters[":USERNAME"]=userName;
			insertStmt.text = sql; 
			// register listeners for the result and failure (status) events 
			insertStmt.addEventListener(SQLEvent.RESULT, insertResult); 
			insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError); 
			// execute the statement 
			insertStmt.execute(); 
		}
		
		/**
		 * 查询数据库表里面的数据
		 * */
		private function select():void{
			var selectStmt:SQLStatement = new SQLStatement(); 
			selectStmt.sqlConnection = conn; 
			var sql:String = "SELECT ID,X,Y,INDEXPAGE,FILEPATH,IMAGE,USERNAME FROM huahuang"; 
			selectStmt.text = sql; 
			selectStmt.addEventListener(SQLEvent.RESULT, resultHandler); 
			selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
			selectStmt.execute(); 
		}
		/**
		 * 上面查询的返回结果
		 * */
		private function resultHandler(event:SQLEvent):void{ 
			var result:SQLResult = event.currentTarget.getResult(); 
			if(result.data!=null){
				var numResults:Number = result.data.length; 
				var array:Array=new Array();
				for (var i:int = 0; i < numResults; i++) 
				{ 
					var row:Object = result.data[i]; 
					var byteImg:ByteArray=new ByteArray();
					byteImg.writeBytes(row.IMAGE); 
					byteImg.position=0;
					array.push({id:row.ID,imageByte:byteImg,_PX:row.X,_PY:row.Y,
						filePath:row.FILEPATH,indexPage:row.INDEXPAGE});
					
					var output:String = "ID:" + row.ID;
					output += "; X:" + row.X;
					output += "; Y: " + row.Y; 
					output += "; INDEXPAGE: "+row.INDEXPAGE;
					output += "; FILEPATH: " + row.FILEPATH; 
					output += "; USERNAME: " + row.USERNAME; 
					trace(output); 
				} 
				bookArray=new ArrayCollection(array)
				if(bookArray.length>0){
					if(fitement==null)
						fitement=new FitmentSHG();
					fitement.fitmentBook(bookArray);
				}
			}else{
				trace("没有可查询的数据");
			}
		} 
		/**
		 * 根据书籍的ID号查询index
		 * */
		public function selectForID(id:String,readTextBook:ReadTextBook):void{
			this.readTextBook=readTextBook;
			var selectStmt:SQLStatement = new SQLStatement(); 
			selectStmt.sqlConnection = conn; 
			var sql:String = "SELECT INDEXPAGE FROM huahuang WHERE id='"+id+"'"; 
			selectStmt.text = sql; 
			selectStmt.addEventListener(SQLEvent.RESULT, resultIndex); 
			selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
			selectStmt.execute();
		}
		/**
		 * 返回数据结果
		 * */
		private function resultIndex(event:SQLEvent):void{
			var result:SQLResult = event.currentTarget.getResult(); 
			if(result.data!=null){
				var numResults:Number = result.data.length; 
				var array:Array=new Array();
				for (var i:int = 0; i < numResults; i++) 
				{ 
					var row:Object = result.data[i]; 
					readTextBook.index = row.INDEXPAGE;
				} 
			}else{
				trace("没有可查询的数据");
			}
		}
		/**
		 * 更新数据库表里面的数据
		 **/
		public function update(ID:String,X:int,Y:int):void{
			var updateStmt:SQLStatement = new SQLStatement(); 
			updateStmt.sqlConnection = conn; 
			var salary:Number=8000;
			var sql:String = "UPDATE huahuang SET X="+X+",Y="+Y+" where ID='"+ID+"'";
			updateStmt.text = sql; 
			// register listeners for the result and failure (status) events 
			updateStmt.addEventListener(SQLEvent.RESULT, updateStmtHandler); 
			updateStmt.addEventListener(SQLErrorEvent.ERROR, updateStmtError); 
			// execute the statement 
			updateStmt.execute(); 
		} 
		/**
		 * 更新数据库表里面的数据，把当前这本书籍翻到那一页
		 * 保存进入数据库表
		 * */
		public function updatePageIndex(ID:String,pageIndex:int):void{
			var updateStmt:SQLStatement = new SQLStatement(); 
			updateStmt.sqlConnection = conn; 
			var sql:String = "UPDATE huahuang SET INDEXPAGE="+pageIndex+" where ID='"+ID+"'";
			updateStmt.text = sql; 
			// register listeners for the result and failure (status) events 
			updateStmt.addEventListener(SQLEvent.RESULT, updateStmtHandler); 
			updateStmt.addEventListener(SQLErrorEvent.ERROR, updateStmtError); 
			// execute the statement 
			updateStmt.execute(); 
		}
		/**
		 * 删除数据库表里面的数据
		 * */
		private function delect(bookID:String):void{
			var detectStmt:SQLStatement = new SQLStatement(); 
			detectStmt.sqlConnection = conn; 
			var sql:String = "DELETE FROM huahuang WHERE ID='"+bookID+"'"; 
			detectStmt.text = sql; 
			// register listeners for the result and failure (status) events 
			detectStmt.addEventListener(SQLEvent.RESULT, detectHandler); 
			detectStmt.addEventListener(SQLErrorEvent.ERROR, detectError); 
			// execute the statement 
			detectStmt.execute(); 
		}
		private function openFailure(event:SQLErrorEvent):void{
			trace("操作数据文件产生异常！"+event.toString());
		}
		
		private function createResult(event:SQLEvent):void 
		{ 
			trace("Table created"); 
		} 
		
		private function createError(event:SQLErrorEvent):void 
		{ 
			trace("Error message:", event.error.message); 
			trace("Details:", event.error.details); 
		} 
		
		private function detectHandler(event:SQLEvent):void{
			trace("删除数据成功！");
		}
		private function detectError(event:SQLErrorEvent):void{
			trace(" 删除数据产生异常!");
		}
		private function insertResult(event:SQLEvent):void 
		{ 
			trace("INSERT statement succeeded"); 
		} 
		
		private function insertError(event:SQLErrorEvent):void 
		{ 
			trace("Error message:", event.error.message); 
			trace("Details:", event.error.details); 
		} 
		private function errorHandler(event:SQLErrorEvent):void{ 
			trace("搜索数据产生异常！");
		}
		private function updateStmtHandler(event:SQLEvent):void{
			trace("更新数据成功!!");
		}
		private function updateStmtError(event:SQLErrorEvent):void{
			trace("更新数据失败");
		}
	}
}